﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.WebPartPages;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Reflection;
using System.Web.UI.HtmlControls;
using DevSky.WebParts.DBConnectWP;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Microsoft.SharePoint;
using System.Web.UI.WebControls.WebParts;
using System.Web;

namespace DevSky.WebParts.WPToolParts
{
  public enum CONNECTMODE : short
  {
    NONE = 0,
    INTEGRATED = 1,
    DBUSER = 2,
    CONFIGCON = 3,
    APPCONKEY = 4
  }
  public enum CHANGETYPE : short
  {
    NONE,
    DBCONNECTIONMODE = 1,
    DBOBJECTTYPE = 2,
    DBOBJECT = 3,
    DBNAME = 4,
    DBUSERNAME = 5,
    DBPASSWORD = 6,
  }

  public class DBConfigSettings : EditorPart
  {
    //private const string CONNECTINFO = "deskyconnectinfo";
    #region CONTROLS
    private Panel mainContainer = null;
    private TextBox txtDBServer = null;
    private TextBox txtDataBase = null;
    private TextBox txtDBUser = null;
    private TextBox txtDBPWd = null;
    private DropDownList ddConnectMode = null;
    private DropDownList ddWCDBConnect = null;
    private DropDownList ddWCAppKey = null;
    private DropDownList ddDataSourceType = null;
    private DropDownList ddObjectSourceObject = null;
    private Button btnLoadDBObjects = null;

    private string savedConnectString = string.Empty;
    private bool isError = false;
    #endregion

    public DBConfigSettings(string title)
    {
      Title = title;
    }

    protected override void OnInit(EventArgs e)
    {
      base.OnInit(e);

      mainContainer = new Panel() { ID = "MainContainer" };
      ddConnectMode = new DropDownList() { ID = "ddConnectMode", AutoPostBack = true };
      ddWCDBConnect = new DropDownList() { ID = "ddWCDBConnect" };
      ddWCAppKey = new DropDownList() { ID = "ddWCAppKey" };
      ddDataSourceType = new DropDownList() { ID = "ddDataSourceType", AutoPostBack = true };
      ddObjectSourceObject = new DropDownList() { ID = "ddObjectSourceObject" };
      //ddObjectSourceObject.SelectedIndexChanged += new EventHandler(ddObjectSourceObject_SelectedIndexChanged);

      txtDataBase = new TextBox() { ID = "txtDataBase" };
      txtDBServer = new TextBox() { ID = "txtDBServer" };
      txtDBUser = new TextBox() { ID = "txtDBUser" };
      txtDBPWd = new TextBox() { ID = "txtDBPWd", TextMode = TextBoxMode.Password };

      ddConnectMode.Items.Add(new ListItem("Please choose one mode ...", CONNECTMODE.NONE.ToString()));
      ddConnectMode.Items.Add(new ListItem("Integrated Security", CONNECTMODE.INTEGRATED.ToString()));
      ddConnectMode.Items.Add(new ListItem("Database User (plain password)", CONNECTMODE.DBUSER.ToString()));
      ddConnectMode.Items.Add(new ListItem("web.config Connection Information", CONNECTMODE.CONFIGCON.ToString()));
      ddConnectMode.Items.Add(new ListItem("web.config APP-Key value", CONNECTMODE.APPCONKEY.ToString()));
      ddConnectMode.SelectedIndexChanged += new EventHandler(ddConnectMode_SelectedIndexChanged);

      ddDataSourceType.Items.Add(new ListItem("Select db object type ...", string.Empty));
      ddDataSourceType.Items.Add(new ListItem("Table", "TABLE"));
      ddDataSourceType.Items.Add(new ListItem("View", "VIEW"));
      ddDataSourceType.Items.Add(new ListItem("Stored Procedure", "SP"));
      ddDataSourceType.SelectedIndexChanged += new EventHandler(ddDataSourceType_SelectedIndexChanged);

      btnLoadDBObjects = new Button() { ID = "btnLoadDBObjects", Text = "Connect to DataBase" };
      btnLoadDBObjects.Click += new EventHandler(btnLoadDBObjects_Click);      

      txtDBPWd.Attributes.Add("value", txtDBPWd.Text);

      LoadDataBaseObjects((WebPartToEdit as DevSkyDBConnectWP).DataSourceType, true);
    }

    void btnLoadDBObjects_Click(object sender, EventArgs e)
    {
      LoadDataBaseObjects(ddDataSourceType.SelectedValue, false);
    }

    void ddObjectSourceObject_SelectedIndexChanged(object sender, EventArgs e)
    {
      ResetConfiguration(CHANGETYPE.DBOBJECT);
    }

    void ddConnectMode_SelectedIndexChanged(object sender, EventArgs e)
    {
      ResetConfiguration(CHANGETYPE.DBCONNECTIONMODE);
    }

    void ddDataSourceType_SelectedIndexChanged(object sender, EventArgs e)
    {
      ResetConfiguration(CHANGETYPE.DBOBJECTTYPE);
    }

    private void LoadDataBaseObjects(string selectObjectType, bool useSavedConnectInfo)
    {
      string lastError = string.Empty;
      string connectInfo = (WebPartToEdit as DevSkyDBConnectWP).ConnectString;
      if (!useSavedConnectInfo)
        connectInfo = ConnectionString;
      if (!string.IsNullOrEmpty(connectInfo))
      {
        SqlConnection cnn = new SqlConnection(connectInfo); // TODO CHECK ...                
        DataTable dtObjects = null;
        try
        {
          cnn.Open();
          if (selectObjectType.Equals("TABLE"))
            dtObjects = cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" });
          if (selectObjectType.Equals("VIEW"))
            dtObjects = cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Views);
          if (selectObjectType.Equals("SP"))
            dtObjects = cnn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Procedures);
        }
        catch (Exception exc)
        {
          lastError = exc.Message;
        }
        finally
        {
          cnn.Close();
        }
        if (dtObjects != null)
        {
          // http://msdn.microsoft.com/de-de/library/ms254969(VS.80).aspx
          ddObjectSourceObject.Items.Clear();
          ddObjectSourceObject.Items.Add(new ListItem("Please choose one entry ...", string.Empty));
          foreach (System.Data.DataRow table in dtObjects.Rows)
          {
            if (selectObjectType.Equals("TABLE"))
              ddObjectSourceObject.Items.Add(new ListItem(table["table_name"].ToString(), table["table_name"].ToString()));
            if (selectObjectType.Equals("SP"))
              ddObjectSourceObject.Items.Add(new ListItem(table["routine_name"].ToString(), table["routine_name"].ToString()));
            if (selectObjectType.Equals("VIEW"))
              ddObjectSourceObject.Items.Add(new ListItem(table["table_name"].ToString(), table["table_name"].ToString()));

          }
        }
      }
      if (!string.IsNullOrEmpty(lastError))
        throw new Exception(lastError);
    }

    private string ConnectionString
    {
      get
      {
        string connectInfo = string.Empty;

        if (ddConnectMode.SelectedValue == CONNECTMODE.INTEGRATED.ToString())
          connectInfo = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", txtDBServer.Text, txtDataBase.Text);
        if (ddConnectMode.SelectedValue == CONNECTMODE.DBUSER.ToString())
        {
          if (string.IsNullOrEmpty(txtDBPWd.Text) && (!string.IsNullOrEmpty(savedConnectString)))
          {
            string password = string.Empty;
            string[] configs = savedConnectString.Split(new char[] { ';' });
            foreach (string entry in configs)
              if (entry.StartsWith("Password="))
                password = entry.Split(new char[] { '=' })[1];
            connectInfo = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", txtDBServer.Text, txtDataBase.Text, txtDBUser.Text, password);
          }
          else
            connectInfo = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", txtDBServer.Text, txtDataBase.Text, txtDBUser.Text, txtDBPWd.Text);
        }
        if (ddConnectMode.SelectedValue == CONNECTMODE.APPCONKEY.ToString())
          connectInfo = ConfigurationManager.AppSettings[ddWCAppKey.SelectedValue];
        if (ddConnectMode.SelectedValue == CONNECTMODE.CONFIGCON.ToString())
          connectInfo = ConfigurationManager.ConnectionStrings[ddWCDBConnect.SelectedValue].ConnectionString;

        return connectInfo;
      }
    }

    protected override void CreateChildControls()
    {
      base.CreateChildControls();            
      UpdatePanel ajaxPanel = new UpdatePanel();

      HtmlTable layout = new HtmlTable();
      layout.Rows.Add(CreatePropertyEntry(ddConnectMode, "Connection mode:"));
      layout.Rows.Add(CreatePropertyEntry(txtDBServer, "MS SQL Server name:"));
      layout.Rows.Add(CreatePropertyEntry(txtDataBase, "Database name:"));
      layout.Rows.Add(CreatePropertyEntry(txtDBUser, "DB Username:"));
      layout.Rows.Add(CreatePropertyEntry(txtDBPWd, "DB User Password:"));
      layout.Rows.Add(CreatePropertyEntry(ddWCDBConnect, "Connection name"));
      layout.Rows.Add(CreatePropertyEntry(ddWCAppKey, "APP-Key name:"));

      layout.Rows.Add(CreatePropertyEntry(ddDataSourceType, "DB object source type:"));
      layout.Rows.Add(CreatePropertyEntry(btnLoadDBObjects, "Try to load database objects"));
      layout.Rows.Add(CreatePropertyEntry(ddObjectSourceObject, "DB object name:"));

      ajaxPanel.ContentTemplateContainer.Controls.Add(layout);
      Controls.Add(ajaxPanel);
      // MAZ ADD PROGRESS ANIMATION
      //Controls.Add(layout);            

      HtmlGenericControl link = new HtmlGenericControl("input");
      link.Attributes.Add("value", "Configure Columns and Details");
      link.Attributes.Add("type", "button");
      link.Attributes.Add("onClick", OpenPopUpCommand("Please define the columns sorting and groups for the view", "/_layouts/DevSkyDBConnectWP/DBViewConfiguration.aspx"));
      Controls.Add(link);      
    }

    #region APPLY CHANGES
    public override bool ApplyChanges()
    {
      DevSkyDBConnectWP parent = WebPartToEdit as DevSkyDBConnectWP;
      if (parent != null)
      {               
        EnsureChildControls();
        RefreshDialogInfo(parent);
        if (parent.DataSourceType != ddDataSourceType.SelectedValue || parent.DataSource != ddObjectSourceObject.SelectedValue)
        {
          parent.CustomDataColumns = null;
          parent.ShowCustomLinks = false;
          parent.ShowDeleteLink = false;
          parent.ShowEditLink = false;
          parent.ShowNewLink = false;
          parent.ShowReadOnlyLink = false;
        }

        parent.DataBaseServer = txtDBServer.Text;
        parent.DataBaseName = txtDataBase.Text;
        parent.DataBaseUser = txtDBUser.Text;
        parent.DataBasePassword = txtDBPWd.Text;
        parent.ConnectMode = ddConnectMode.SelectedValue;
        parent.AppKey = ddWCAppKey.SelectedValue;
        parent.ConnectKey = ddWCDBConnect.SelectedValue;
        parent.ConnectString = ConnectionString;
        
        parent.DataSourceType = ddDataSourceType.SelectedValue;
        parent.DataSource = ddObjectSourceObject.SelectedValue;

        parent.RefreshDataGrid();

        return true;
      }
      return false;
    }

    private void SetStoredValues()
    {
      DevSkyDBConnectWP parent = WebPartToEdit as DevSkyDBConnectWP;
      if (parent != null)
      {        
        EnsureChildControls();
        txtDBServer.Text = parent.DataBaseServer;
        txtDBUser.Text = parent.DataBaseUser;
        txtDBPWd.Text = parent.DataBasePassword;
        txtDBPWd.Attributes.Add("value", parent.DataBasePassword);
        txtDataBase.Text = parent.DataBaseName;
        ddConnectMode.SelectedValue = parent.ConnectMode;
        ddWCAppKey.SelectedValue = parent.AppKey;
        ddWCDBConnect.SelectedValue = parent.ConnectKey;
        ddDataSourceType.SelectedValue = parent.DataSourceType;       
        savedConnectString = parent.ConnectString;
        
        if (ddObjectSourceObject != null && ddObjectSourceObject.Items.Count > 0)
          ddObjectSourceObject.SelectedValue = parent.DataSource;
      }
    }

    public override void SyncChanges()
    {
      if (!isError)
      {
        try
        {
          ReadConfigData();          
          SetStoredValues();
        }
        catch (Exception ex)
        {
          HandleException(ex);
        }
      }
    }
    #endregion

    private void ReadConfigData()
    {
      ddWCAppKey.Items.Clear();
      ddWCDBConnect.Items.Clear();
      ddWCAppKey.Items.Add("Please choose one entry ...");
      ddWCDBConnect.Items.Add("Please choose one entry ...");
      foreach (string entry in ConfigurationManager.AppSettings)
        ddWCAppKey.Items.Add(entry);
      foreach (ConnectionStringSettings entry in ConfigurationManager.ConnectionStrings)
        ddWCDBConnect.Items.Add(entry.Name);
    }

    private void ResetConfiguration(CHANGETYPE changeMode)
    {
      DevSkyDBConnectWP parent = WebPartToEdit as DevSkyDBConnectWP;
      if (parent != null)
      {
        if (changeMode == CHANGETYPE.DBCONNECTIONMODE || changeMode == CHANGETYPE.DBNAME)
        {
          txtDataBase.Text = string.Empty;
          txtDBPWd.Text = string.Empty;
          txtDBServer.Text = string.Empty;
          txtDBUser.Text = string.Empty;
          ddDataSourceType.SelectedValue = string.Empty;
          ddObjectSourceObject.Items.Clear();          
          ddWCAppKey.SelectedIndex = -1;
          ddWCDBConnect.SelectedIndex = -1;
        }
        if (changeMode == CHANGETYPE.DBOBJECTTYPE)
        {
          parent.DataSourceType = string.Empty;
          parent.DataSource = string.Empty;
          txtDBPWd.Attributes.Add("value", txtDBPWd.Text);
          LoadDataBaseObjects(ddDataSourceType.SelectedValue, false);
        }
        if (changeMode == CHANGETYPE.DBOBJECT)
        {
          txtDBPWd.Attributes.Add("value", txtDBPWd.Text);
          parent.DataSource = string.Empty;
        }
        ReadConfigData();
      }
    }

    private void SaveChanges(string webpartID)
    {
      SPFile file = SPContext.Current.Web.GetFile(HttpContext.Current.Request.Url.ToString());
      SPLimitedWebPartManager manager = file.GetLimitedWebPartManager(PersonalizationScope.Shared);
      System.Web.UI.WebControls.WebParts.WebPart webpart = manager.WebParts[webpartID];
      if (webpart != null)
      {
        try
        {
          SPContext.Current.Web.AllowUnsafeUpdates = true;
          manager.SaveChanges(webpart);
        }
        finally
        {
          SPContext.Current.Web.AllowUnsafeUpdates = false;
        }
      }
    }

    private void RefreshDialogInfo(DevSkyDBConnectWP webpart)
    {
      SPFile file = SPContext.Current.Web.GetFile(HttpContext.Current.Request.Url.ToString());
      SPLimitedWebPartManager manager = file.GetLimitedWebPartManager(PersonalizationScope.Shared);
      DevSkyDBConnectWP savedWP = (manager.WebParts[webpart.ID] as DevSkyDBConnectWP);
      if (webpart != null)
      {
        webpart.CustomDataColumns = savedWP.CustomDataColumns;
        webpart.ShowCustomLinks = savedWP.ShowCustomLinks;
        webpart.ShowDeleteLink = savedWP.ShowDeleteLink;
        webpart.ShowEditLink = savedWP.ShowEditLink;
        webpart.ShowNewLink = savedWP.ShowNewLink;
        webpart.ShowReadOnlyLink = savedWP.ShowReadOnlyLink;
      }
    }

    private HtmlTableRow CreatePropertyEntry(Control ctl, string description)
    {
      HtmlTableRow row = new HtmlTableRow();
      HtmlTableCell cell = new HtmlTableCell();
      row.Controls.Add(cell);
      HtmlGenericControl div = new HtmlGenericControl("DIV");
      div.Attributes.Add("class", "UserSectionHead");
      cell.Controls.Add(div);
      div.Controls.Add(new Label() { Text = description });
      div = new HtmlGenericControl("DIV");
      div.Attributes.Add("class", "UserSectionBody");
      cell.Controls.Add(div);
      HtmlGenericControl control = new HtmlGenericControl("DIV");
      control.Attributes.Add("class", "UserControlGroup");
      control.Controls.Add(ctl);
      div.Controls.Add(control);
      return row;
    }

    // http://www.chakkaradeep.com/post/Using-the-SharePoint-2010-Modal-Dialog.aspx
    private string OpenPopUpCommand(string title, string url)
    {
      return string.Format("javascript:SP.UI.ModalDialog.showModalDialog({{ url: \"{0}\", width: 800, height: 600, title: \"{1}\" }}); return false;", url, title);            
    }

    private void HandleException(Exception ex)
    {
      isError = true;
      Panel error = new Panel() { ID = "error" };
      error.Attributes.Add("style", "padding: 5px;");
      Label lblError = new Label() { Text = ex.Message };
      lblError.Attributes.Add("style", "color: red; font-weight: bold;");
      error.Controls.Add(lblError);
      this.Controls.AddAt(0, error);
    }
  }
}
